﻿CREATE PROCEDURE reports.s_Payments_DPD AS

DECLARE @DateFrom smalldatetime,@DateTo smalldatetime SELECT @DateFrom = '1/1/2007',@DateTo = GETDATE()
DECLARE @CutOffDays tinyint SET @CutOffDays = 7;

DROP TABLE reports.t_Payment_DPD
SELECT * INTO #Payment FROM usix.t_Payment P
WHERE	
P.[Payment Date]BETWEEN @DateFrom AND @DateTo AND
DATEDIFF(dd, P.[Payment Due Date], P.[Payment Date]) BETWEEN 1 AND 180 AND
ISNUMERIC(P.[Receipt #]) = 1 AND
P.[Payment Type] = 1AND
P.Balance > 10;

WITH PMTs AS(
SELECT     P.[Id #], P.CompanyID, P.[Receipt #],
MAX(P.[Payment Due Date])[Payment Due Date],
MAX(P.[Payment Date])[Payment Date],
MAX(P.[Payment Amount])[Payment Amount],
MAX(NoteDate0)NoteDate,
convert(float,COUNT(*)) AS Count
FROM(
SELECT DISTINCT t_Note.LoanID, t_Note.NoteDate0
FROM         t_Note INNER JOIN
                      v_ADS_CollectorsBySAM ON t_Note.NoteUser = v_ADS_CollectorsBySAM.sAMAccountName
WHERE     (t_Note.NoteDate0 BETWEEN @DateFrom AND @DateTo)
) AS COLL_NOTES
RIGHT OUTER JOIN usix.t_CustomerFile_Export AS C_F ON COLL_NOTES.LoanID = C_F.LoanID
RIGHT OUTER JOIN #Payment AS P ON C_F.[Customer #] = P.[Id #] AND C_F.CompanyID_Raw = P.CompanyID AND
COLL_NOTES.NoteDate0 BETWEEN dbo.MaxDate(P.[Payment Due Date]+1,P.[Payment Date]-CONVERT(int,@CutOffDays)) AND
														 P.[Payment Date]
GROUP BY [Id #], P.CompanyID, P.[Receipt #]
)
SELECT PMTs.*,TOTALS.TotalCount,convert(numeric(10,1),PMTs.Count/TOTALS.TotalCount)Participation
INTO reports.t_Payment_DPD
FROM PMTs
INNER JOIN
(SELECT [Id #], [Receipt #] ,SUM(Count) TotalCount FROM PMTs GROUP BY [Id #], [Receipt #])TOTALS
	ON PMTs.[Id #] = TOTALS.[Id #] AND PMTs.[Receipt #] = TOTALS.[Receipt #]
ORDER BY [Id #], [Payment Date]

DROP TABLE #Payment

